package control;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.*;

public class KasirKontrol {

    private Connection conn;

    public KasirKontrol(Connection koneksi) {
        this.conn = koneksi;
    }

    public static KasirKontrol getKoneksiKasir() throws SQLException {
        KasirKontrol kon = new KasirKontrol(OraConnection.getKoneksi());
        return kon;
    }

    public List<Nota> tampilDaftarTagihan() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

        conn.setAutoCommit(false);
        String sql = "SELECT nota.idnota, pegawai.idpegawai, pegawai.namapegawai, pembeli.nomeja, "
                + "pembeli.idpembeli, pembeli.namapembeli, nota.waktu "
                + "from nota,pegawai,pembeli "
                + "where nota.pegawai=pegawai.idpegawai and "
                + "nota.pembeli=pembeli.idpembeli";
        statement = conn.prepareStatement(sql);
        result = statement.executeQuery();
        List<Nota> notaList = new ArrayList<Nota>();
        while (result.next()) {
            Nota nota = new Nota();
            nota.setIdNota(result.getString("idNota"));
            Pegawai pegawai = new Pegawai();
            pegawai.setIdPegawai(result.getString("idpegawai"));
            pegawai.setNamaPegawai(result.getString("namapegawai"));
            nota.setPegawai(pegawai);
            Pembeli pembeli = new Pembeli();
            pembeli.setNoMeja(result.getString("noMeja"));
            pembeli.setNamaPembeli(result.getString("namapembeli"));
            nota.setPembeli(pembeli);
            nota.setWaktu(result.getDate("waktu"));
            notaList.add(nota);
        }
        conn.commit();
        return notaList;
    }

    public String cariNota(int noMeja, String waktu) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        String idNota = "";
        try {
            conn.setAutoCommit(false);
            String sql = "SELECT idNota FROM nota WHERE noMeja = ? and waktu = ?";

            statement = conn.prepareStatement(sql);
            statement.setInt(1, noMeja);
            statement.setString(2, waktu);
            result = statement.executeQuery();
            while (result.next()) {
                idNota = result.getString("idNota");
            }
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(OraConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return idNota;
    }

    public List<Nota> updateHargaTotal(String idNota, String noMeja, String idPegawai) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

        conn.setAutoCommit(false);
        String sql = "SELECT n.idNota, n.pegawai, n.pembeli, n.waktu, b.idPembeli, b.namaPembeli, b.noMeja, "
                + "g.idPegawai, g.namaPegawai, g.username, g.password, g.divisi, "
                + "m.idMenu, m.namaMenu, m.hargaMenu, m.kategori, "
                + "p.idPesan, p.waktuPesan, p.pembeli, p.menu, p.jumlahPesanan, p.status "
                + "FROM nota n, pegawai g, pembeli b, menu m, pesan p WHERE n.pembeli = b.idPembeli "
                + "AND n.pegawai = g.idPegawai AND p.menu = m.idMenu AND p.pembeli = b.idPembeli "
                + "AND p.menu = m.idMenu "
                + "AND n.idNota = '" + idNota + "' AND b.noMeja = '" + noMeja + "' AND g.idPegawai = '" + idPegawai + "'";
        statement = conn.prepareStatement(sql);
        result = statement.executeQuery();
        List<Nota> notaList = new ArrayList<Nota>();
        double total = 0;
        double harga = 0;
        while (result.next()) {
            Nota nota = new Nota();
            nota.setIdNota(result.getString("idNota"));
            Pegawai peg = new Pegawai();
            peg.setIdPegawai(result.getString("idPegawai"));
            peg.setNamaPegawai(result.getString("namaPegawai"));
            nota.setPegawai(peg);
            Pembeli pem = new Pembeli();
            pem.setIdPembeli(result.getString("idPembeli"));
            pem.setNamaPembeli(result.getString("namaPembeli"));
            nota.setPembeli(pem);
            nota.setWaktu(result.getDate("waktu"));
            harga = result.getDouble("hargaMenu");
            total = total + harga;
            nota.setHargaTotal(harga);
            harga = 0;
            notaList.add(nota);
        }
//        String sql2 = "update nota set hargaTotal = " + total 
//                + " where idNota = '" + idNota + "' ";
//        Statement stat2 = conn.createStatement();
//        stat2.executeUpdate(sql2);
//        conn.close();
        cek(idNota, total);
        System.out.println(total);
        conn.commit();
        return notaList;
    }
    
    public void cek(String id, double total) throws SQLException{
        try {
            String sql = "update nota set hargaTotal = " + total
                    + "where idNota= '" + id + "'";
            Statement stat = conn.createStatement();
            stat.executeUpdate(sql);
//            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(KasirKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
